with    three_segment_different as (
         select express_org_code, express_center_short_code
         from jms_dim.dim_yl_sys_package_different_base
         group by express_org_code, express_center_short_code
     ),

     three_segment_site as
         (
             select
                 t1.name,
                 t1.code,
                 t1.agent_code,
                 t1.agent_name,
                 t1.provider_id,
                 t1.provider_desc,
                 t1.city_id,
                 t1.city_desc,
                 t1.area_id,
                 t1.area_desc,
                 max(t2.express_center_short_code) as first_code_package,
                 max(t3.code) as sj_second_code,
                 concat_ws(',',collect_set(t4.code)) as first_code
             from
                 jms_dim.dim_network_whole_massage t1
                     left join three_segment_different t2 on t1.code=t2.express_org_code
                     left join jms_dim.dim_lmdm_sys_second_code t3 on t1.code=t3.network_code and  t3.is_enable='1' and t3.is_delete='1'
                     left join jms_dim.dim_lmdm_sys_first_code t4 on t1.provider_id=t4.destination_province_id
                     and t1.city_id=t4.destination_city_id and t1.area_id =t4.destination_area_id
                     and  t4.is_enable='1' and t4.is_delete='1'
             group by
                 t1.name,
                 t1.code,
                 t1.agent_code,
                 t1.agent_name,
                 t1.provider_id,
                 t1.provider_desc,
                 t1.city_id,
                 t1.city_desc,
                 t1.area_id,
                 t1.area_desc
         )
,dwd_yl_oms_order_third_ext_base_dt  as 
(
select 
     t.order_id
    ,max(t.station_unique_number) station_unique_number
    ,max(t.station_code ) station_code
from jms_dwd.dwd_yl_oms_order_third_ext_base_dt t 
where t.dt >= date_add('{{ execution_date  | cst_ds }}', -30) and t.dt <= '{{ execution_date  | cst_ds }}'
 group by t.order_id

) 
insert overwrite table jms_dm.dm_lmdm_four_segment_monitoring_detail_dt partition (dt)
select
 to_date(sign_time) as date_time
,t.waybill_no               --运单号
,case when t.order_source_code  ='D09' then '桃花岛'
      when t.order_source_code  ='D801' then '七星潭'
      when t.order_source_code  ='D67' then '紫金山'

      else '云路模型'
   end as  order_source_code     --订单来源
,t.sign_time                --签收时间
,t.sign_finance_code        --签收财务网点编码
,t.sign_finance_name        --签收财务网点名称
,t.sign_network_code        --签收网点code
,t.sign_network_name        --签收网点名称
,t.receiver_province_id     --省份id
,t.receiver_province_name   --省份name
,t.receiver_city_id         --城市id
,t.receiver_city_name       --城市name
,t.receiver_area_id         --区域id
,t.receiver_area_name       --区域name
,case when t2.first_code_package is not  null then t2.first_code_package else t2.first_code end as sj_first_code --签收一段码
,t2.sj_second_code --签收二段码
,t4.tail_code  as sj_third_code --签收三段码
,t5.courier_station_name  --签收驿站名称
,t5.input_type_name    --签收驿站品牌
,t.station_code   --签收驿站
,t.station_unique_number  --签收驿站唯一标识
,t6.station_code   as plan_station_code  --预测驿站码
,t6.station_unique_number as plan_station_unique_number  --预测驿站唯一标识
,t5.courier_station_code  --签收驿站编码
--2023-07-18新增字段
,tt.agent_code    as sign_agent_code     --代理区code
,tt.agent_name    as sign_agent_name     --代理区name
,case when t.order_source_code  ='D09' then  'D09'
      when t.order_source_code  ='D801' then 'D801'
      when t.order_source_code  ='D67' then 'D67'
      else '99-99-99'
   end as  order_source     --订单来源

,to_date(sign_time) as dt
from  jms_dwd.dwd_yl_oms_oms_waybill_incre_dt t
left join  three_segment_site t2 on t.dispatch_network_code=t2.code
left join jms_dim.dim_network_whole_massage  tt on t.sign_network_code=tt.code
left join  (
             select  t1.staff_code,
                     t1.tail_code
             from jms_dim.dim_lmdm_sys_tail_code t
                      join jms_dim.dim_lmdm_sys_tail_code_staff t1 on t.id=t1.tail_id
             where   t.is_enable='1' and t.is_delete='1'
             group by  t1.staff_code, t1.tail_code
         ) t4 on t.dispatch_staff_code=t4.staff_code
         
left join jms_dim.dim_yl_lmdm_agent_point t5 on  t.station_unique_number=t5.unique_identify
left join dwd_yl_oms_order_third_ext_base_dt t6 on  t.order_id=t6.order_id

where t.dt between date_add('{{ execution_date  | cst_ds }}', -30) and '{{ execution_date  | cst_ds }}'
and to_date(sign_time)='{{ execution_date  | cst_ds }}'
and t.is_sign='1'
and t.is_refund='0'
and t.is_void='0'
and t.station_unique_number is not null
distribute by pmod(hash(rand()),100)

;
